﻿<cfsilent>
	<cfscript>
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		
		examId = event.getArg("EXID");
		
		/* 加载考试任务 */
		sql = "SELECT  
					t_examtask.term_id, t_examtask.cid 
				FROM 
					t_examtask 
				WHERE 
					t_examtask.task_exid = :taskExid ";
					
		queryObj = new query( datasource=application.dnsSlave );
		
		queryObj.addParam( name="taskExid", value=examId, cfsqltype="cf_sql_char" );
		
		rs_examTask = queryObj.execute( sql=sql ).getResult();
		
		sql = "SELECT 
					term_start, academic_year, academic_term 
				FROM 
					t_term 
				WHERE 
					term_id = :termId ";
		
		queryObj = new query( datasource=application.dnsSlave ); 
		
		queryObj.addParam( name="termId", value=rs_examTask.term_id, cfsqltype="cf_sql_char" ); 
		
		rs_term = queryObj.execute( sql=sql ).getResult(); 
		
		termstart = rs_term.term_start;
		
		sql = "SELECT 
					course_name 
				FROM 
					t_course 
				WHERE 
					cid = :courseId ";
		
		queryObj = new query( datasource=application.dnsSlave ); 
		
		queryObj.addParam( name="courseId", value=rs_examTask.cid, cfsqltype="cf_sql_varchar" ); 
		
		rs_course = queryObj.execute( sql=sql ).getResult(); 
		
		examType = event.getArg("ExamType");
		if(examType eq "F"){
			examName="期末考试";
		}else if(examType eq "M"){
			examName="期中考试";
		}else if(examType eq "D"){
			examName="分散考试";
		}
		downFile = URLEncodedFormat( rs_term.academic_year & "-" & rs_term.academic_term & "," & rs_course.course_name & examName & "安排", "utf-8");
		
		/* 载入相关操作组件 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		/* 生成电子表格数据 */
		sql = "SELECT 
					t_institute.institute_name, 
					t_course.course_name, 
					t_course.course_credit, 
					CASE WHEN t_course.course_code IS NULL THEN '-' ELSE t_course.course_code END course_code, 
					t_examtask.task_exid, 
					t_examtask.wid, 
					t_examtask.wdidx, 
					t_examcell.cellidx, 
					t_examcell.time_start, 
					t_examcell.time_end, 
					t_classroom.classroom, 
					t_classroom.cr_floor, 
					t_building.b_name, 
					COUNT(*) countEx 
				FROM 
					t_student_exam 
					INNER JOIN t_examtask ON t_examtask.task_exid = t_student_exam.task_exid 
					INNER JOIN t_course ON t_course.cid = t_examtask.cid 
					INNER JOIN t_institute ON t_institute.institute_id = t_course.institute_id 
					INNER JOIN t_examcell ON t_examcell.cellidx = t_examtask.cellidx 
					INNER JOIN t_classroom ON t_classroom.cr_id = t_student_exam.cr_id 
					INNER JOIN t_building ON t_building.b_id = t_classroom.b_id 
				WHERE 
					t_examtask.task_exid = :examId 
					AND 
					t_examtask.exam_type = :examType 
				GROUP BY 
					t_institute.institute_name, 
					t_course.course_name, 
					t_course.course_credit, 
					course_code, 
					t_examtask.task_exid, 
					t_examtask.wid, 
					t_examtask.wdidx, 
					t_examcell.cellidx, 
					t_examcell.time_start, 
					t_examcell.time_end, 
					t_classroom.classroom, 
					t_classroom.cr_floor, 
					t_building.b_name 
				ORDER BY 
					t_building.b_name,
					t_classroom.cr_floor, 
					t_classroom.classroom ";
				  
		queryObj = new query( datasource=application.dnsSlave ); 
		
		queryObj.addParam( name="examId", value=examId, cfsqltype="cf_sql_char" ); 
		queryObj.addParam( name="examType", value=examType, cfsqltype="cf_sql_char" ); 
		
		rs_exam = queryObj.execute( sql=sql ).getResult(); 
		
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("考场分布情况", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "任课单位,课程,学分,课程号,考试编号,考试时间,考试周次,场次安排,起止时间,考场,教学楼,所在楼层,参考人数");
		
		/* 写入考场安排 */
		for ( row=1; row LTE rs_exam.recordCount; row++){
	
			rowData = rs_exam["institute_name"][row]
						& "," & rs_exam["course_name"][row] 
						& "," & numberFormat( rs_exam["course_credit"][row], "_.__" ) 
						& "," & rs_exam["course_code"][row] 
						& "," & rs_exam["task_exid"][row]
						& "," & datetimeAdvice.formatDateString( mathAdvice.revertTaskWeekDay( termstart, rs_exam["wid"][row], rs_exam["wdidx"][row] ), "YYYY年MM月DD日") & " (" & datetimeAdvice.formatTimeString( rs_exam["time_start"][row], "HH:MI" ) & "~" & datetimeAdvice.formatTimeString( rs_exam["time_end"][row], "HH:MI" ) & ")"
						& "," & rs_exam["wid"][row] 
						& "," & mathAdvice.convertWeekDayToChinese( rs_exam["wdidx"][row] ) & mathAdvice.convertExamIdxToChinese( rs_exam["cellidx"][row] ) 
						& "," & datetimeAdvice.formatTimeString( rs_exam["time_start"][row], "HH:MI" ) & "~" & datetimeAdvice.formatTimeString( rs_exam["time_end"][row], "HH:MI" )
						& "," & rs_exam["classroom"][row] 
						& "," & rs_exam["b_name"][row] 
						& "," & dictAdvice.getFloorString( rs_exam["cr_floor"][row] ) 
						& "," & rs_exam["countEx"][row];
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData); 
			
		}
		
		/* 创建学生详细名单 */
		excel.SpreadsheetCreateSheet (spreadsheetObj, "学生名单");
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "学生名单");
		
		sql = "SELECT 
					t_building.b_name, 
					t_classroom.classroom, 
					t_student_exam.sitidx, 
					t_student.stu_id, 
					t_student.stu_name, 
					t_student.stu_sex, 
					t_class.cls_name, 
					t_class.grade, 
					t_subject.sbj_short, 
					t_institute.institute_short 
				FROM 
					t_student_exam 
					INNER JOIN t_classroom ON t_classroom.cr_id = t_student_exam.cr_id 
					INNER JOIN t_building ON t_building.b_id = t_classroom.b_id 
					INNER JOIN t_student ON t_student.stu_id = t_student_exam.stu_id 
					INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
					INNER JOIN t_subject ON t_subject.sbj_id = t_class.sbj_id 
					INNER JOIN t_institute ON t_institute.institute_id = t_subject.institute_id 
				WHERE 
					t_student_exam.task_exid = :examId 
				ORDER BY 
					t_building.b_name, t_classroom.classroom, t_student_exam.sitidx "; 
		
		queryObj = new query( datasource=application.dnsSlave ); 
		queryObj.addParam( name="examId", value=examId, cfsqltype="cf_sql_char" );
		
		rs_student = queryObj.execute( sql=sql ).getResult(); 
		
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "教学楼,考场,座次,学号,姓名,性别,班级,年级,专业,学院");
		
		for ( i=1; i LTE rs_student.recordCount; i++){
			
			rowData = rs_student["b_name"][i]
						& "," & rs_student["classroom"][i] 
						& "," & rs_student["sitidx"][i] 
						& "," & rs_student["stu_id"][i] 
						& "," & rs_student["stu_name"][i];
			
			if ( rs_student["stu_sex"][i] eq "1" ) {
				rowData = rowData & ",男";
			}
			else {
				rowData = rowData & ",女";
			}
			 
			rowData = rowData
						& "," & rs_student["cls_name"][i] 
						& "," & rs_student["grade"][i] 
						& "," & rs_student["sbj_short"][i] 
						& "," & rs_student["institute_short"][i]; 
			
			/* 写入学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
				
		}
	
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "考场分布情况");
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
	
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
</cfsilent>
